/**
 * 使用POI导出数据到excel文件
 * @author lld
 * @date 2014-1-14
 * */
package com.douyu.water.util;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.io.File;
import java.io.InputStream;
import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Method;
import java.util.Calendar;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;


public class CreateExcelPoi 
{
  /**
   * 导出文件的保存路径
   * */
	private String fileSavePath = "/files/";
	/**
	 * 导出文件sheet的名字
	 * */	
	private String fileName;	
	/**
	 * 列名数组，名称跟字段名数组相对应
	 * */
    private String[] columnName;
    /**
     * 字段名数组，名称跟列名数组相对应
     * */
    private String[] filedName;
    /**
     * 要遍历的对象列表
     * */
    private List list;

    /**
     * 要遍历的对象的类型
     * */
    private Object cls;
    /**
     * 要替换的属性列表,格式如下：
     * #sex:0,男;1,女;#flag:Y,有效;N,无效;#……#
     * 该字符串以#开头，以#结束
     * */
    private String replace;
    
    /**
     * 关联列
     * */
    private int[] refindex;
    /**
     * 关联条件
     * */
    private String[] refcon;
    /**
     * 列的数据类型
     * */
    private String[] cellType;
    
	public String[] getCellType() {
		return cellType;
	}

	public void setCellType(String[] cellType) {
		this.cellType = cellType;
	}

	public int[] getRefindex() {
		return refindex;
	}

	public void setRefindex(int[] refindex) {
		this.refindex = refindex;
	}

	public String[] getRefcon() {
		return refcon;
	}

	public void setRefcon(String[] refcon) {
		this.refcon = refcon;
	}

	public String getReplace() {
		return replace;
	}

	public void setReplace(String replace) {
		this.replace = replace;
	}

	public Object getCls() {
		return cls;
	}

	public void setCls(Object cls) {
		this.cls = cls;
	}

	public String[] getColumnName() 
	{
		return columnName;
	}
	
	public void setColumnName(String[] columnName) 
	{
		this.columnName = columnName;
	}
	
	public String[] getFiledName() 
	{
		return filedName;
	}
	
	public void setFiledName(String[] filedName) 
	{
		this.filedName = filedName;
	}
    
	public String getFileName() {
		return fileName;
	}

	public void setFileName(String fileName) {
		this.fileName = fileName;
	}

	public List getList() {
		return list;
	}

	public void setList(List list) {
		this.list = list;
	}

	public String getFileSavePath() {
		return fileSavePath;
	}

	public void setFileSavePath(String fileSavePath) {
		this.fileSavePath = fileSavePath;
	}
	/**
	 * 获取内容，并保存到指定的文件中
	 * @author lld
	 * @date 2014-01-14
	 * @return 返回文件的绝对路径
	 * */
	public String saveExcel()throws Exception
	{
		String file = null;
		if(this.fileName == null || this.filedName == null || this.columnName == null || this.cls == null)
		{
			return file;
		}
		//获取excel操作对象
		HSSFWorkbook book = new HSSFWorkbook();
		//获取sheet对象
		HSSFSheet sheet = book.createSheet(this.fileName);
		//形成数据表头
		HSSFRow title = sheet.createRow(0);
		//设置数据表头的数据对齐方式为剧中对齐
		HSSFCellStyle style = book.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		//开始设置数据表头
		HSSFCell cell = null;
		for(int i = 0;i < this.columnName.length;i++)
		{
			sheet.setColumnWidth(i, this.columnName[i].length() * 256 * 3);			
			cell = title.createCell(i);
			cell.setCellValue(this.columnName[i]);			
			cell.setCellStyle(style);
			
		}
		//将数据写入excel文件中
		int size = list.size();
		HSSFRow row = null;
		for(int i = 0;i < size;i++)
		{
			Object obj = list.get(i);
			row = sheet.createRow(i + 1);
			//利用java反射机制获取每一列的值
			for(int j = 0;j < filedName.length;j++)
			{
				cell = row.createCell(j);
				if(filedName[j]== null)
				{
					continue;
				}
				//是否有附加条件
				boolean flag = true;
				if(refindex != null && refindex[j]>0&&refcon != null && refcon[j]!=null)
				{
					//获取附加条件
					int index = refindex[j];
					String field = filedName[index-1];
					String methodname = "get" + field.toUpperCase().charAt(0)+field.substring(1);					
					Class<?> objcls = obj.getClass();
					Method method = objcls.getDeclaredMethod(methodname,new Class[0]);
					Object getobj = method.invoke(obj);
					if(getobj instanceof Date)
					{
						getobj = new SimpleDateFormat("yyyy-MM-dd").format((Date)getobj);						
					}
					String getVal = getobj.toString();
					if(!getVal.trim().equals(refcon[j].trim()))
					{
						//不为当前列赋值
						flag = false;
					}
				}
				if(flag)
				{
					String field = filedName[j];				
					String methodname = "get" + field.toUpperCase().charAt(0)+field.substring(1);					
					Class<?> objcls = obj.getClass();
					Method method = objcls.getDeclaredMethod(methodname,new Class[0]);
					Object getobj = method.invoke(obj);
					if(getobj instanceof Date)
					{
						getobj = new SimpleDateFormat("yyyy-MM-dd").format((Date)getobj);						
					}
					//将符号替换为汉字
					if(getobj != null)
					{
						if(this.replace != null && this.replace.indexOf(field.trim())>0)
						{				
							String sub = this.replace.substring(this.replace.indexOf(field.trim()),
									this.replace.indexOf("#",this.replace.indexOf(field.trim())+field.trim().length()));							
							getobj = sub.substring(sub.indexOf(getobj.toString().trim())+getobj.toString().trim().length()+1,
									 sub.indexOf(";",sub.indexOf(getobj.toString().trim())+getobj.toString().trim().length()));
						}
						if(cellType != null && cellType.length>=j && cellType[j] != null && !cellType[j].trim().equals(""))
						{
							String type = cellType[j].trim();
							if(type.equalsIgnoreCase("string"))
							{
								//cell.setCellType(HSSFCell.CELL_TYPE_STRING);
								String temp = null;
								if(getobj instanceof Date)
								{
									temp = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(getobj);
								}
								else
								{
									temp = getobj.toString();
								}
								cell.setCellValue(temp);
							}
							else if(type.equalsIgnoreCase("number"))
							{
								//cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
								cell.setCellValue(Double.parseDouble(getobj.toString().trim()));
							}
							/*else if(type.equalsIgnoreCase("blank"))
							{
								cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
							}
							else if(type.equalsIgnoreCase("boolean"))
							{
								cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
							}
							else if(type.equalsIgnoreCase("formula"))
							{
								cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
							}*/
							else
							{
								cell.setCellValue(getobj.toString());
							}
						}
						else
						{
							cell.setCellValue(getobj.toString());
						}
						
					}
				}								
			}
		}
		//将文件保存到指定位置
		try
		{
			File file_dir = new File(this.fileSavePath);
			if(!file_dir.exists())
			{				
				file_dir.mkdirs();				
			}
			String excelName = String.valueOf(Calendar.getInstance().getTimeInMillis());			
			FileOutputStream out = new FileOutputStream(this.fileSavePath + excelName + ".xls");			
			book.write(out);			
			out.close();
			file = this.fileSavePath + excelName + ".xls";
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return file;
	}
	/**
	 * 获取生成的Excel文件的文件流，在读取完文件后，将文件删除
	 * @param delete 是否在读取完后删除文件
	 * @return boolean 是否删除成功
	 * */
	public InputStream getExcelStream(boolean delete)throws Exception
	{
		String tempFileName = this.saveExcel();
		if(tempFileName == null)
		{
			System.out.println("Can not create the excel file!");
			return null;
		}				
		File _file = new File(tempFileName);
		byte[] data = null;
		if(_file.exists())
		{
		  FileInputStream  in = new FileInputStream(_file);
		  data = new byte[in.available()];
		  in.read(data);		 
		  in.close();
		  if(delete)
		  {
			  _file.delete();
		  }
		}
		if(data != null)
		{			
			return new ByteArrayInputStream(data);
		}
		else
		{
			return new ByteArrayInputStream("".getBytes());
		}
	}	
}
